﻿using System;
using System.Collections;
using System.Data;
using CE.Database;

/// <summary>
/// Summary description for HFVariableDetailsDB
/// </summary>
/// 
namespace CE.Admin
{
    public class HFVariableDetailsDB
    {
        public HFVariableDetailsDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Int64 Insert(HFVariableDetails hfvariabledetails)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "[sprocHFVariableDetailsInsertUpdateSingleItem]";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@PVTHFVariableId", hfvariabledetails.PVTHFVariableId);
            _sPara.Add("@HDBHFVariableId", hfvariabledetails.HDBHFVariableId);
            _sPara.Add("@HFPackageId", hfvariabledetails.HFPackageId);
            _sPara.Add("@HFProviderId", hfvariabledetails.HFProviderId);
            _sPara.Add("@ProductName", hfvariabledetails.ProductName);
            _sPara.Add("@LoanPurpose", hfvariabledetails.LoanPurpose);
            _sPara.Add("@PropertyStatus", hfvariabledetails.PropertyStatus);
            _sPara.Add("@InterestRateType", hfvariabledetails.InterestRateType);
            _sPara.Add("@HasMinimumLoanAmount", hfvariabledetails.HasMinimumLoanAmount);
            _sPara.Add("@MinimumLoanAmount", hfvariabledetails.MinimumLoanAmount);
            _sPara.Add("@HasPolicyPeriod", hfvariabledetails.HasPolicyPeriod);
            _sPara.Add("@PolicyPeriodFrom", hfvariabledetails.PolicyPeriodFrom);
            _sPara.Add("@PolicyPeriodTo", hfvariabledetails.PolicyPeriodTo);
            _sPara.Add("@LockIn", hfvariabledetails.LockIn);

            _sPara.Add("@PVTPropertyType", hfvariabledetails.PVTPropertyType);
            _sPara.Add("@PVTMinLoanTenure", hfvariabledetails.PVTMinLoanTenure);
            _sPara.Add("@PVTMaxLoanTenure", hfvariabledetails.PVTMaxLoanTenure);
            _sPara.Add("@PVTMinAgeApplicant", hfvariabledetails.PVTMinAgeApplicant);
            _sPara.Add("@PVTMaxAgeApplicant", hfvariabledetails.PVTMaxAgeApplicant);
            _sPara.Add("@PVTMaxMarginFin", hfvariabledetails.PVTMaxMarginFin);
            _sPara.Add("@PVTFullRedPen", hfvariabledetails.PVTFullRedPen);
            _sPara.Add("@PVTLegalFeeSubsidy", hfvariabledetails.PVTLegalFeeSubsidy);
            _sPara.Add("@PVTInterestY1", hfvariabledetails.PVTInterestY1);
            _sPara.Add("@PVTDescriptionY1", hfvariabledetails.PVTDescriptionY1);
            _sPara.Add("@PVTInterestY2", hfvariabledetails.PVTInterestY2);
            _sPara.Add("@PVTDescriptionY2", hfvariabledetails.PVTDescriptionY2);
            _sPara.Add("@PVTInterestY3", hfvariabledetails.PVTInterestY3);
            _sPara.Add("@PVTDescriptionY3", hfvariabledetails.PVTDescriptionY3);
            _sPara.Add("@PVTInterestThereAfter", hfvariabledetails.PVTInterestThereAfter);
            _sPara.Add("@PVTDescriptionThereAfter", hfvariabledetails.PVTDescriptionThereAfter);
            _sPara.Add("@PVTMoreInfo", hfvariabledetails.PVTMoreInfo);
            _sPara.Add("@PVTStatus", hfvariabledetails.PVTStatus);

            _sPara.Add("@HDBPropertyType", hfvariabledetails.HDBPropertyType);
            _sPara.Add("@HDBMinLoanTenure", hfvariabledetails.HDBMinLoanTenure);
            _sPara.Add("@HDBMaxLoanTenure", hfvariabledetails.HDBMaxLoanTenure);
            _sPara.Add("@HDBMinAgeApplicant", hfvariabledetails.HDBMinAgeApplicant);
            _sPara.Add("@HDBMaxAgeApplicant", hfvariabledetails.HDBMaxAgeApplicant);
            _sPara.Add("@HDBMaxMarginFin", hfvariabledetails.HDBMaxMarginFin);
            _sPara.Add("@HDBFullRedPen", hfvariabledetails.HDBFullRedPen);
            _sPara.Add("@HDBLegalFeeSubsidy", hfvariabledetails.HDBLegalFeeSubsidy);
            _sPara.Add("@HDBInterestY1", hfvariabledetails.HDBInterestY1);
            _sPara.Add("@HDBDescriptionY1", hfvariabledetails.HDBDescriptionY1);
            _sPara.Add("@HDBInterestY2", hfvariabledetails.HDBInterestY2);
            _sPara.Add("@HDBDescriptionY2", hfvariabledetails.HDBDescriptionY2);
            _sPara.Add("@HDBInterestY3", hfvariabledetails.HDBInterestY3);
            _sPara.Add("@HDBDescriptionY3", hfvariabledetails.HDBDescriptionY3);
            _sPara.Add("@HDBInterestThereAfter", hfvariabledetails.HDBInterestThereAfter);
            _sPara.Add("@HDBDescriptionThereAfter", hfvariabledetails.HDBDescriptionThereAfter);
            _sPara.Add("@HDBMoreInfo", hfvariabledetails.HDBMoreInfo);
            _sPara.Add("@HDBStatus", hfvariabledetails.HDBStatus);

            _sPara.Add("@RefCEUserId", hfvariabledetails.RefCEUserId);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public HFVariableDetails GetVariableDetails(Int64 HFPackageId)
        {
            HFVariableDetails _hfvariabledetails = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocHFVariableDetailsSelectItem";

            SortedList _spara = new SortedList();
            _spara.Add("@HFPackageId", HFPackageId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _hfvariabledetails = new HFVariableDetails();
                    _hfvariabledetails = FillDataRecord(_dt, 0);
                }
            }
            return _hfvariabledetails;
        }

        private HFVariableDetails FillDataRecord(DataTable myDataTable, int RowId)
        {
            HFVariableDetails myVariable = new HFVariableDetails();
            
            

            myVariable.HFPackageId = Convert.ToInt64(myDataTable.Rows[0]["HFPackageId"].ToString());
            myVariable.HFProviderId = Convert.ToInt64(myDataTable.Rows[0]["HFProviderId"].ToString());
            myVariable.ProductName = myDataTable.Rows[0]["ProductName"].ToString();
            myVariable.LoanPurpose = Convert.ToInt16(myDataTable.Rows[0]["LoanPurpose"].ToString());
            myVariable.PropertyStatus = Convert.ToInt16(myDataTable.Rows[0]["PropertyType"].ToString());
            myVariable.InterestRateType = Convert.ToInt16(myDataTable.Rows[0]["PropertyStatus"].ToString());
            myVariable.HasMinimumLoanAmount = Convert.ToBoolean(myDataTable.Rows[0]["HasMinimumLoanAmount"].ToString());
            myVariable.MinimumLoanAmount = Convert.ToSingle(myDataTable.Rows[0]["MinimumLoanAmount"].ToString());
            myVariable.HasPolicyPeriod = Convert.ToBoolean(myDataTable.Rows[0]["HasPolicyPeriod"].ToString());
            myVariable.PolicyPeriodFrom = Convert.ToDateTime(myDataTable.Rows[0]["PolicyPeriodFrom"].ToString());
            myVariable.PolicyPeriodTo = Convert.ToDateTime(myDataTable.Rows[0]["PolicyPeriodTo"].ToString());
            myVariable.LockIn = Convert.ToInt16(myDataTable.Rows[0]["LockIn"].ToString());
            if (myDataTable.Rows.Count == 2)
            {
                if (Convert.ToInt16(myDataTable.Rows[0]["PropertyType"].ToString()) == 1)
                {
                    myVariable.PVTHFVariableId = Convert.ToInt64(myDataTable.Rows[0]["HFVariableId"].ToString());
                    myVariable.PVTPropertyType = Convert.ToInt16(myDataTable.Rows[0]["PropertyType"].ToString());
                    myVariable.PVTMinLoanTenure = Convert.ToInt32(myDataTable.Rows[0]["MinLoanTenure"].ToString());
                    myVariable.PVTMaxLoanTenure = Convert.ToInt32(myDataTable.Rows[0]["MaxLoanTenure"].ToString());
                    myVariable.PVTMinAgeApplicant = Convert.ToInt32(myDataTable.Rows[0]["MinAgeApplicant"].ToString());
                    myVariable.PVTMaxAgeApplicant = Convert.ToInt32(myDataTable.Rows[0]["MaxAgeApplicant"].ToString());
                    myVariable.PVTMaxMarginFin = Convert.ToInt16(myDataTable.Rows[0]["MaxMarginFin"].ToString());
                    myVariable.PVTFullRedPen = myDataTable.Rows[0]["FullRedPen"].ToString();
                    myVariable.PVTLegalFeeSubsidy = myDataTable.Rows[0]["LegalFeeSubsidy"].ToString();
                    myVariable.PVTInterestY1 = Convert.ToSingle(myDataTable.Rows[0]["InterestY1"].ToString());
                    myVariable.PVTDescriptionY1 = myDataTable.Rows[0]["DescriptionY1"].ToString();
                    myVariable.PVTInterestY2 = Convert.ToSingle(myDataTable.Rows[0]["InterestY2"].ToString());
                    myVariable.PVTDescriptionY2 = myDataTable.Rows[0]["DescriptionY2"].ToString();
                    myVariable.PVTInterestY3 = Convert.ToSingle(myDataTable.Rows[0]["InterestY3"].ToString());
                    myVariable.PVTDescriptionY3 = myDataTable.Rows[0]["DescriptionY3"].ToString();
                    myVariable.PVTInterestThereAfter = Convert.ToSingle(myDataTable.Rows[0]["InterestThereAfter"].ToString());
                    myVariable.PVTDescriptionThereAfter = myDataTable.Rows[0]["DescriptionThereAfter"].ToString();
                    myVariable.PVTMoreInfo = myDataTable.Rows[0]["MoreInfo"].ToString();
                    myVariable.PVTStatus = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
                }
                else if (Convert.ToInt16(myDataTable.Rows[0]["PropertyType"].ToString()) == 2)
                {
                    myVariable.HDBHFVariableId = Convert.ToInt64(myDataTable.Rows[0]["HFVariableId"].ToString());
                    myVariable.HDBPropertyType = Convert.ToInt16(myDataTable.Rows[0]["PropertyType"].ToString());
                    myVariable.HDBMinLoanTenure = Convert.ToInt32(myDataTable.Rows[0]["MinLoanTenure"].ToString());
                    myVariable.HDBMaxLoanTenure = Convert.ToInt32(myDataTable.Rows[0]["MaxLoanTenure"].ToString());
                    myVariable.HDBMinAgeApplicant = Convert.ToInt32(myDataTable.Rows[0]["MinAgeApplicant"].ToString());
                    myVariable.HDBMaxAgeApplicant = Convert.ToInt32(myDataTable.Rows[0]["MaxAgeApplicant"].ToString());
                    myVariable.HDBMaxMarginFin = Convert.ToInt16(myDataTable.Rows[0]["MaxMarginFin"].ToString());
                    myVariable.HDBFullRedPen = myDataTable.Rows[0]["FullRedPen"].ToString();
                    myVariable.HDBLegalFeeSubsidy = myDataTable.Rows[0]["LegalFeeSubsidy"].ToString();
                    myVariable.HDBInterestY1 = Convert.ToSingle(myDataTable.Rows[0]["InterestY1"].ToString());
                    myVariable.HDBDescriptionY1 = myDataTable.Rows[0]["DescriptionY1"].ToString();
                    myVariable.HDBInterestY2 = Convert.ToSingle(myDataTable.Rows[0]["InterestY2"].ToString());
                    myVariable.HDBDescriptionY2 = myDataTable.Rows[0]["DescriptionY2"].ToString();
                    myVariable.HDBInterestY3 = Convert.ToSingle(myDataTable.Rows[0]["InterestY3"].ToString());
                    myVariable.HDBDescriptionY3 = myDataTable.Rows[0]["DescriptionY3"].ToString();
                    myVariable.HDBInterestThereAfter = Convert.ToSingle(myDataTable.Rows[0]["InterestThereAfter"].ToString());
                    myVariable.HDBDescriptionThereAfter = myDataTable.Rows[0]["DescriptionThereAfter"].ToString();
                    myVariable.HDBMoreInfo = myDataTable.Rows[0]["MoreInfo"].ToString();
                    myVariable.HDBStatus = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
                }
                if (Convert.ToInt16(myDataTable.Rows[1]["PropertyType"].ToString()) == 1)
                {
                    myVariable.PVTHFVariableId = Convert.ToInt64(myDataTable.Rows[1]["HFVariableId"].ToString());
                    myVariable.PVTPropertyType = Convert.ToInt16(myDataTable.Rows[1]["PropertyType"].ToString());
                    myVariable.PVTMinLoanTenure = Convert.ToInt32(myDataTable.Rows[1]["MinLoanTenure"].ToString());
                    myVariable.PVTMaxLoanTenure = Convert.ToInt32(myDataTable.Rows[1]["MaxLoanTenure"].ToString());
                    myVariable.PVTMinAgeApplicant = Convert.ToInt32(myDataTable.Rows[1]["MinAgeApplicant"].ToString());
                    myVariable.PVTMaxAgeApplicant = Convert.ToInt32(myDataTable.Rows[1]["MaxAgeApplicant"].ToString());
                    myVariable.PVTMaxMarginFin = Convert.ToInt16(myDataTable.Rows[1]["MaxMarginFin"].ToString());
                    myVariable.PVTFullRedPen = myDataTable.Rows[1]["FullRedPen"].ToString();
                    myVariable.PVTLegalFeeSubsidy = myDataTable.Rows[1]["LegalFeeSubsidy"].ToString();
                    myVariable.PVTInterestY1 = Convert.ToSingle(myDataTable.Rows[1]["InterestY1"].ToString());
                    myVariable.PVTDescriptionY1 = myDataTable.Rows[1]["DescriptionY1"].ToString();
                    myVariable.PVTInterestY2 = Convert.ToSingle(myDataTable.Rows[1]["InterestY2"].ToString());
                    myVariable.PVTDescriptionY2 = myDataTable.Rows[1]["DescriptionY2"].ToString();
                    myVariable.PVTInterestY3 = Convert.ToSingle(myDataTable.Rows[1]["InterestY3"].ToString());
                    myVariable.PVTDescriptionY3 = myDataTable.Rows[1]["DescriptionY3"].ToString();
                    myVariable.PVTInterestThereAfter = Convert.ToSingle(myDataTable.Rows[1]["InterestThereAfter"].ToString());
                    myVariable.PVTDescriptionThereAfter = myDataTable.Rows[1]["DescriptionThereAfter"].ToString();
                    myVariable.PVTMoreInfo = myDataTable.Rows[1]["MoreInfo"].ToString();
                    myVariable.PVTStatus = Convert.ToBoolean(myDataTable.Rows[1]["Status"].ToString());
                }
                else if (Convert.ToInt16(myDataTable.Rows[1]["PropertyType"].ToString()) == 2)
                {
                    myVariable.HDBHFVariableId = Convert.ToInt64(myDataTable.Rows[1]["HFVariableId"].ToString());
                    myVariable.HDBPropertyType = Convert.ToInt16(myDataTable.Rows[1]["PropertyType"].ToString());
                    myVariable.HDBMinLoanTenure = Convert.ToInt32(myDataTable.Rows[1]["MinLoanTenure"].ToString());
                    myVariable.HDBMaxLoanTenure = Convert.ToInt32(myDataTable.Rows[1]["MaxLoanTenure"].ToString());
                    myVariable.HDBMinAgeApplicant = Convert.ToInt32(myDataTable.Rows[1]["MinAgeApplicant"].ToString());
                    myVariable.HDBMaxAgeApplicant = Convert.ToInt32(myDataTable.Rows[1]["MaxAgeApplicant"].ToString());
                    myVariable.HDBMaxMarginFin = Convert.ToInt16(myDataTable.Rows[1]["MaxMarginFin"].ToString());
                    myVariable.HDBFullRedPen = myDataTable.Rows[1]["FullRedPen"].ToString();
                    myVariable.HDBLegalFeeSubsidy = myDataTable.Rows[1]["LegalFeeSubsidy"].ToString();
                    myVariable.HDBInterestY1 = Convert.ToSingle(myDataTable.Rows[1]["InterestY1"].ToString());
                    myVariable.HDBDescriptionY1 = myDataTable.Rows[1]["DescriptionY1"].ToString();
                    myVariable.HDBInterestY2 = Convert.ToSingle(myDataTable.Rows[1]["InterestY2"].ToString());
                    myVariable.HDBDescriptionY2 = myDataTable.Rows[1]["DescriptionY2"].ToString();
                    myVariable.HDBInterestY3 = Convert.ToSingle(myDataTable.Rows[1]["InterestY3"].ToString());
                    myVariable.HDBDescriptionY3 = myDataTable.Rows[1]["DescriptionY3"].ToString();
                    myVariable.HDBInterestThereAfter = Convert.ToSingle(myDataTable.Rows[1]["InterestThereAfter"].ToString());
                    myVariable.HDBDescriptionThereAfter = myDataTable.Rows[1]["DescriptionThereAfter"].ToString();
                    myVariable.HDBMoreInfo = myDataTable.Rows[1]["MoreInfo"].ToString();
                    myVariable.HDBStatus = Convert.ToBoolean(myDataTable.Rows[1]["Status"].ToString());
                }

            }
            else if (myDataTable.Rows.Count == 1)
            {
                if (Convert.ToInt16(myDataTable.Rows[0]["PropertyType"].ToString()) == 1)
                {
                    myVariable.PVTHFVariableId = Convert.ToInt64(myDataTable.Rows[0]["HFVariableId"].ToString());
                    myVariable.PVTPropertyType = Convert.ToInt16(myDataTable.Rows[0]["PropertyType"].ToString());
                    myVariable.PVTMinLoanTenure = Convert.ToInt32(myDataTable.Rows[0]["MinLoanTenure"].ToString());
                    myVariable.PVTMaxLoanTenure = Convert.ToInt32(myDataTable.Rows[0]["MaxLoanTenure"].ToString());
                    myVariable.PVTMinAgeApplicant = Convert.ToInt32(myDataTable.Rows[0]["MinAgeApplicant"].ToString());
                    myVariable.PVTMaxAgeApplicant = Convert.ToInt32(myDataTable.Rows[0]["MaxAgeApplicant"].ToString());
                    myVariable.PVTMaxMarginFin = Convert.ToInt16(myDataTable.Rows[0]["MaxMarginFin"].ToString());
                    myVariable.PVTFullRedPen = myDataTable.Rows[0]["FullRedPen"].ToString();
                    myVariable.PVTLegalFeeSubsidy = myDataTable.Rows[0]["LegalFeeSubsidy"].ToString();
                    myVariable.PVTInterestY1 = Convert.ToSingle(myDataTable.Rows[0]["InterestY1"].ToString());
                    myVariable.PVTDescriptionY1 = myDataTable.Rows[0]["DescriptionY1"].ToString();
                    myVariable.PVTInterestY2 = Convert.ToSingle(myDataTable.Rows[0]["InterestY2"].ToString());
                    myVariable.PVTDescriptionY2 = myDataTable.Rows[0]["DescriptionY2"].ToString();
                    myVariable.PVTInterestY3 = Convert.ToSingle(myDataTable.Rows[0]["InterestY3"].ToString());
                    myVariable.PVTDescriptionY3 = myDataTable.Rows[0]["DescriptionY3"].ToString();
                    myVariable.PVTInterestThereAfter = Convert.ToSingle(myDataTable.Rows[0]["InterestThereAfter"].ToString());
                    myVariable.PVTDescriptionThereAfter = myDataTable.Rows[0]["DescriptionThereAfter"].ToString();
                    myVariable.PVTMoreInfo = myDataTable.Rows[0]["MoreInfo"].ToString();
                    myVariable.PVTStatus = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
                }
                else if (Convert.ToInt16(myDataTable.Rows[0]["PropertyType"].ToString()) == 2)
                {
                    myVariable.HDBHFVariableId = Convert.ToInt64(myDataTable.Rows[0]["HFVariableId"].ToString());
                    myVariable.HDBPropertyType = Convert.ToInt16(myDataTable.Rows[0]["PropertyType"].ToString());
                    myVariable.HDBMinLoanTenure = Convert.ToInt32(myDataTable.Rows[0]["MinLoanTenure"].ToString());
                    myVariable.HDBMaxLoanTenure = Convert.ToInt32(myDataTable.Rows[0]["MaxLoanTenure"].ToString());
                    myVariable.HDBMinAgeApplicant = Convert.ToInt32(myDataTable.Rows[0]["MinAgeApplicant"].ToString());
                    myVariable.HDBMaxAgeApplicant = Convert.ToInt32(myDataTable.Rows[0]["MaxAgeApplicant"].ToString());
                    myVariable.HDBMaxMarginFin = Convert.ToInt16(myDataTable.Rows[0]["MaxMarginFin"].ToString());
                    myVariable.HDBFullRedPen = myDataTable.Rows[0]["FullRedPen"].ToString();
                    myVariable.HDBLegalFeeSubsidy = myDataTable.Rows[0]["LegalFeeSubsidy"].ToString();
                    myVariable.HDBInterestY1 = Convert.ToSingle(myDataTable.Rows[0]["InterestY1"].ToString());
                    myVariable.HDBDescriptionY1 = myDataTable.Rows[0]["DescriptionY1"].ToString();
                    myVariable.HDBInterestY2 = Convert.ToSingle(myDataTable.Rows[0]["InterestY2"].ToString());
                    myVariable.HDBDescriptionY2 = myDataTable.Rows[0]["DescriptionY2"].ToString();
                    myVariable.HDBInterestY3 = Convert.ToSingle(myDataTable.Rows[0]["InterestY3"].ToString());
                    myVariable.HDBDescriptionY3 = myDataTable.Rows[0]["DescriptionY3"].ToString();
                    myVariable.HDBInterestThereAfter = Convert.ToSingle(myDataTable.Rows[0]["InterestThereAfter"].ToString());
                    myVariable.HDBDescriptionThereAfter = myDataTable.Rows[0]["DescriptionThereAfter"].ToString();
                    myVariable.HDBMoreInfo = myDataTable.Rows[0]["MoreInfo"].ToString();
                    myVariable.HDBStatus = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
                }
            }
            
            return myVariable;
        }

        //public DataTable GetVariableDetailsList()
        //{
        //    DataTable _hfvlist = null;
        //    MyDatabase _db = new MyDatabase();
        //    string _spName = "sprocHFVariableDetailsSelectItemList";

        //    using (DataTable _dt = _db.GetDataTableBySP(_spName))
        //    {
        //        if (_dt != null && _dt.Rows.Count > 0)
        //        {
        //            _hfvlist = _dt;
        //        }
        //    }
        //    return _hfvlist;
        //}

        //public DataTable GetVariableDetailsList(Int64 HFProviderId)
        //{
        //    DataTable _hfvlist = null;
        //    MyDatabase _db = new MyDatabase();
        //    string _spName = "sprocHFVariableDetailsSelectItemList";

        //    SortedList _spara = new SortedList();
        //    _spara.Add("@HFProviderId", HFProviderId);

        //    using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
        //    {
        //        if (_dt != null && _dt.Rows.Count > 0)
        //        {
        //            _hfvlist = _dt;
        //        }
        //    }
        //    return _hfvlist;
        //}

    }
}